<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <title>sql translate api</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="shortcut icon" href="./favicon.ico">
    <script type="text/javascript" src="js/plugin/jquery.min.js"></script>
    <script type="text/javascript" src="js/plugin/fullscreen.js"></script>
    <script type="text/javascript" src="js/apiTest.js"></script>
    <script type="text/javascript" src="js/plugin/bootstrap-3.3.5/js/bootstrap.min.js"></script>
    <script type="text/javascript" src="js/plugin/bootstrap-3.3.5/js/bootstrap-select.min.js"></script>
    <script type="text/javascript" src="js/plugin/bootstrap-3.3.5/js/defaults-zh_CN.min.js"></script>
    <script type="text/javascript" src="js/plugin/jsonview-v1.2.0/dist/jquery.jsonview.js"></script>
    <script type="text/javascript" src="js/plugin/page.js"></script>
    <script type="text/javascript" src="js/sybn_bootstrap.js"></script>
    <script type="text/javascript" src="js/sybn_common.js"></script>
    <script type="text/javascript" src="js/sybn_desc_table.js"></script>
    <script type="text/javascript" src="js/plugin/sql/codemirror.js"></script>
    <script type="text/javascript" src="js/plugin/sql/matchbrackets.js"></script>
    <script type="text/javascript" src="js/plugin/sql/sql.js"></script>
    <script type="text/javascript" src="js/plugin/sql/show-hint.js"></script>
    <script type="text/javascript" src="js/plugin/sql/sql-hint3.js"></script>
    <script type="text/javascript" src="js/plugin/sql/sql-hint-sybn.js"></script>
    <script type="text/javascript" src="js/sybn_demo_sql.js"></script>
    <link rel="stylesheet" href="css/fontawesome-4.2.0_ie7/4.2.0/css/font-awesome.min.css">
    <link rel="stylesheet" href="js/plugin/bootstrap-3.3.5/css/bootstrap.min.css">
    <link rel="stylesheet" href="css/bootstrap_sybn_custom.css">
    <link rel="stylesheet" href="css/api_test.css">
    <link rel="stylesheet" href="css/plugin/sql/codemirror.css"/>
    <link rel="stylesheet" href="css/plugin/sql/show-hint.css"/>
    <style>
		.textarea_p {height: calc(100% - 35px);}
		.textarea_p textarea{height: 100%;width:100%;}
		.panel{height:100%}
		.tab-content{height: calc(100% - 32px)}
		.panel-body{height: calc(100% - 20px)}

    </style>
</head>
<body>
<ul class="nav nav-tabs">
    <li><strong class="title">sql translate api</strong></li>
    <li class="active"><a href="#p_translate_mongo" data-toggle="tab" aria-expanded="false" title="sql select 查询 转 mongo aggregate 查询">sql查询转mongo</a></li>
    <li class=""><a href="#p_translate_es" data-toggle="tab" aria-expanded="false" title="sql select 查询 转 es dsl 查询">sql查询转es</a></li>
    <li class=""><a href="#p_translate_presto" data-toggle="tab" aria-expanded="false" title="sql select 查询 转 presto select 查询">sql查询转presto</a></li>
    <li class=""><a href="#p_translate_insert" data-toggle="tab" aria-expanded="false" title="合并多行insert为单行">合并insert</a></li>
    <li class=""><a href="#p_translate_createTable" data-toggle="tab" aria-expanded="false">建表语句转换</a></li>
    <li class=""><a href="#p_translate_sql" data-toggle="tab" aria-expanded="false">格式化sql</a></li>
    <li class=""><a href="#p_comment_sql" data-toggle="tab" aria-expanded="false">注释sql</a></li>
    <li class=""><a href="#p_translate_callees" data-toggle="tab" aria-expanded="false">清理调用链</a></li>
    <li class=""><a href="#p_translate_getFrom" data-toggle="tab" aria-expanded="false">提取sql表名</a></li>
    <li class=""><a href="#p_info" data-toggle="tab" aria-expanded="false">帮助</a></li>
    <li class="floatRight padding3">
        <!--
         <button type="button" class="btn btn-danger btn-xs" onclick="$('#cacheRefresh').click()">刷新缓存</button>-->
    </li>
</ul>

<div class="tab-content">

    <div id="p_translate_mongo" class="panel panel-primary tab-pane active">
        <div class="panel-heading text-primary">
            <span class="p_title">sql select 查询 转 mongo aggregate 查询</span>
        </div>

        <div class="panel-body sybn-api-demo collapse in">
            <div class="col-xs-12 padding0">
                <span>请在左侧输入sql然后</span>
                <span data-translate="mongo" class="btn btn-success converSql submit btn-xs mongo">点此转换</span>
                <span data-translate="mongo" class="btn btn-success converSql submit btn-xs mongo pretty">点此转换并格式化</span>
                <span>更多信息请查看帮助 (比如:如何查询时间,如何unwind,如何join)</span>
            </div>

            <div class="col-xs-6 padding0 textarea_p">
        <textarea id="sql_textarea_mongo" name="sql" class="sql_textarea" style="margin-top:5px;display: block!important;">
-- 请在此写入想要转换的sql语句(mongo不支持join, 以下是利用lookup在mongo实现的三表联查demo)

# 从 ticket 表统计用户注册地址城市，并显示 city 表中的城名称
select
  city_id, count(*) as count_num,
  (select city_name from city where a.city_id = city.id) as name
from (

  # 获取订单及订单用户的城市id
  select
    user_id,
    (select city_id from user where ticket.user_id= user.id) as city_id # 从用户表找城市id
  from ticket
  where ticket_time >= str_to_date('2019-10-01', '%Y-%m-%d')
    and ticket_time < str_to_date('2019-10-08', '%Y-%m-%d') # Mongo 查时间必须转Date格式

) a group by city_id
        </textarea>
            </div>

            <div class="col-xs-6 padding0 textarea_p">
                <textarea id="aggregate_textarea_mongo" name="output" class="sql_textarea" style="margin-top:5px;display: block!important;">#请点击转换按钮在此查看结果</textarea>
            </div>
        </div>
    </div>

    <div id="p_translate_es" class="panel panel-primary tab-pane">
        <div class="panel-heading text-primary">
            <span class="p_title">[测试版] sql select 查询 转 es dsl 查询 (兼容6.0以上版本)</span>
        </div>

        <div class="panel-body sybn-api-demo collapse in">
            <div class="col-xs-12 padding0">
                <span>请在左侧输入sql然后</span>
                <span data-translate="elasticSearch" class="btn btn-success converSql submit btn-xs es">点此转换</span>
                <span data-translate="elasticSearch" class="btn btn-success converSql submit btn-xs es pretty">点此转换并格式化</span>
                <span>此功能为测试版, 请谨慎测试! 更多信息请查看帮助. 附: curl http://127.0.0.1:9200/_sql -X GET -H 'Content-Type: application/json;charset=UTF-8' -d '转换后内容'</span>
            </div>

            <div class="col-xs-6 padding0 textarea_p">
        <textarea id="sql_textarea_es" name="sql" class="sql_textarea" style="margin-top:5px;display: block!important;">
-- 请在此写入想要转换的sql语句 (es 不支持 join)

select cinema, count(id) cnt
from maoyan_movie_schedule
where id > 1
group by cinema
having cnt > 2
limit 5
        </textarea>
            </div>

            <div class="col-xs-6 padding0 textarea_p">
                <textarea id="aggregate_textarea_es" name="output" class="sql_textarea" style="margin-top:5px;display: block!important;">#请点击转换按钮在此查看结果</textarea>
            </div>
        </div>
    </div>


    <div id="p_translate_presto" class="panel panel-primary tab-pane">
        <div class="panel-heading text-primary">
            <span class="p_title">sql select 查询 转 presto select 查询</span>
        </div>

        <div class="panel-body sybn-api-demo collapse in">

            <div class="col-xs-12 padding0">
                <span>请在左侧输入sql然后</span>
                <span data-translate="presto" class="btn btn-success converSql submit btn-xs presto">点此转换</span>
                <span data-translate="presto" class="btn btn-success converSql submit btn-xs presto pretty">点此转换并格式化</span>
                <span>更多信息请查看帮助</span>
            </div>

            <div class="col-xs-6 padding0 textarea_p">
                <textarea id="sql_textarea_presto" name="sql" class="sql_textarea" style="margin-top:5px;display: block!important;">
-- 请在此写入想要转换的sql语句

# 从 ticket 表统计用户注册地址城市，并显示 city 表中的城名称
select city_id, '`特殊字符串`' as p, count(t.`id`) as `count_num`
from table t
where t.`a` = 1 and `b` = '2' and cast(c as int) = 4 and d is null
group by city_id,p
                </textarea>
            </div>

            <div class="col-xs-6 padding0 textarea_p">
                <textarea id="aggregate_textarea_presto" name="output" class="sql_textarea" style="margin-top:5px;display: block!important;">#请点击转换按钮在此查看结果</textarea>
            </div>
        </div>
    </div>


    <div id="p_translate_postgre" class="panel panel-primary tab-pane">
        <div class="panel-heading text-primary">
            <span class="p_title">sql select 查询 转 postgre select 查询</span>
        </div>

        <div class="panel-body sybn-api-demo collapse in">

            <div class="col-xs-12 padding0">
                <span>请在左侧输入sql然后</span>
                <span data-translate="postgre" class="btn btn-success converSql submit btn-xs postgre">点此转换</span>
                <span data-translate="postgre" class="btn btn-success converSql submit btn-xs postgre pretty">点此转换并格式化</span>
                <span>更多信息请查看帮助</span>
            </div>

            <div class="col-xs-6 padding0 textarea_p">
                <textarea id="sql_textarea_postgre" name="sql" class="sql_textarea" style="margin-top:5px;display: block!important;">
-- 请在此写入想要转换的sql语句

# 从 ticket 表统计用户注册地址城市，并显示 city 表中的城名称
select city_id, '`特殊字符串`' as p, count(t.`id`) as `count_num`
from table t
where t.`a` = 1 and `b` = '2' and cast(c as int) = 4 and d is null
group by city_id,p
                </textarea>
            </div>

            <div class="col-xs-6 padding0 textarea_p">
                <textarea id="aggregate_textarea_postgre" name="output" class="sql_textarea" style="margin-top:5px;display: block!important;">#请点击转换按钮在此查看结果</textarea>
            </div>
        </div>
    </div>

  <div id="p_translate_insert" class="panel panel-primary tab-pane">
    <div class="panel-heading text-primary">
      <span class="p_title">insert 语句合并</span>
    </div>

    <div class="panel-body sybn-api-demo collapse in">

      <div class="col-xs-12 padding0">
        <span>请在左侧输入 insert 然后</span>
        <span data-translate="mergeInsert" class="btn btn-success converSql submit btn-xs mergeInsert">点此合并</span>
        <span data-translate="mergeAlignInsert" class="btn btn-success converSql submit btn-xs mergeInsert pretty">点此合并且对齐</span>
        <span>更多信息请查看帮助</span>
      </div>

      <div class="col-xs-6 padding0 textarea_p">
                <textarea id="sql_textarea_insert" name="sql" class="sql_textarea" style="margin-top:5px;display: block!important;">
INSERT INTO `rpt_base`.`rb_report_col`(`id`, `cinema_code`, `name`) VALUES (1, 111, 'aaaa');
INSERT INTO `rpt_base`.`rb_report_col`(`id`, `cinema_code`, `name`) VALUES (2, 112, 'bbbb');
INSERT INTO `rpt_base`.`rb_report_col`(`id`, `cinema_code`, `name`) VALUES (3, 113, 'cccc');
                </textarea>
      </div>

      <div class="col-xs-6 padding0 textarea_p">
        <textarea id="aggregate_textarea_insert" name="output" class="sql_textarea" style="margin-top:5px;display: block!important;">#请点击转换按钮在此查看结果</textarea>
      </div>
    </div>
  </div>


    <div id="p_translate_createTable" class="panel panel-primary tab-pane">
        <div class="panel-heading text-primary">
            <span class="p_title">createTable 建表转换</span>
        </div>

        <div class="panel-body sybn-api-demo collapse in">

            <div class="col-xs-12 padding0">
                <span>输出类型:</span>
                <select class="dbtype" name="dbtype">
                    <option value="clickhouse" selected="selected">clickhouse</option>
                    <option value="doris">apache doris</option>
                    <option value="flinkSql">flink sql</option>
                    <option value="hive">hive</option>
                    <option value="h2">h2</option>
                    <option value="mysql">mysql</option>
                    <option value="javaBean">javaBean</option>
                    <option value="setAllFields">java setFields</option>
                    <option value="selectAll">select 所有字段</option>
                    <option value="selectAllDesc">select 所有字段及注释</option>
                    <option value="selectDescAll">select 所有字段as中文</option>
                    <option value="selectHumpAll">select 所有字段as驼峰</option>
                    <option value="mybatisInsertSql">mybatis insert into</option>
                    <option value="mybatisInsertOrUpdateSql">mybatis insertOrUpdate ${val}</option>
                    <option value="mybatisInsertOrUpdateSqlParams">mybatis insertOrUpdateParams #{val}</option>
<!--                    <option value="mergeInsert">insert合并</option>-->
<!--                    <option value="mergeAlignInsert">insert合并且对齐列</option>-->
                    <option value="django">python django</option>
                    <option value="datax">datax mysql to mysql</option>
                </select>
                <span> 请在左侧输入create table然后</span>
                <span data-translate="createTable"
                      class="btn btn-success converSql submit btn-xs createTable">点此转换</span>
                <span>转换结果仅供参考, 请按需修改 ENGINE(存储引擎), PARTITION BY(分组键), ORDER BY(主键), SAMPLE BY(抽样键) 等参数</span>
            </div>

            <div class="col-xs-6 padding0 textarea_p textarea_p">
                <textarea id="sql_textarea_createTable" name="sql" class="sql_textarea" style="margin-top:5px;display: block!important;">
-- 请在此写入 mysql / hive / presto / ads 的建表语句, 或者 json 字符串 {a:1,b:'xxx'}

CREATE TABLE `user_info` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(128) DEFAULT NOT NULL COMMENT '姓名',
  `MOBILE_NUM` varchar(20) DEFAULT NOT NULL COMMENT '手机号',
  `BIRTHDAY` date DEFAULT NULL COMMENT '生日',
  `EMAIL` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `SEX` varchar(32) DEFAULT NULL COMMENT '性别',
  `AGE` int(11) DEFAULT NULL COMMENT '年龄',
  `STATUS` tinyint(4) DEFAULT NULL COMMENT '状态[0-正常 1-冻结 2-注销]',
  `CREATE_TIME` datetime DEFAULT NOT NULL COMMENT '创建时间',
  `MODIFY_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE KEY `INDEX_UNIQUE_MOBILE_NUM` (`MOBILE_NUM`) USING BTREE,
  KEY `INDEX_MEMBER_STATUS` (`STATUS`) USING BTREE COMMENT '会员状态索引',
  KEY `IDX_CREATE_TIME` (`CREATE_TIME`) USING BTREE,
  KEY `IDX_MODIFY_TIME` (`MODIFY_TIME`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='会员表';
                </textarea>
            </div>

            <div class="col-xs-6 padding0 textarea_p textarea_p">
                <textarea id="aggregate_textarea_createTable" name="output" class="sql_textarea" style="margin-top:5px;display: block!important;">#请点击转换按钮在此查看结果</textarea>
            </div>
        </div>
    </div>


    <div id="p_translate_sql" class="panel panel-primary tab-pane">
        <div class="panel-heading text-primary">
            <span class="p_title">sql 格式化</span>
        </div>

        <div class="panel-body sybn-api-demo collapse in">

            <div class="col-xs-12 padding0">
                <span>请在左侧输入sql然后</span>
                <span data-translate="sql" class="btn btn-success converSql submit btn-xs sql">点此格式化</span>
                |
                <label>
                    <span>参数化sql:</span>
                    <select class="parameterized" name="parameterized">
                        <option value="true" >是(建常量替换为?)</option>
                        <option value="false" selected="selected" >否(维持原样)</option>
                    </select>
                </label>
                <label>
                    <span>select每行:</span>
                    <select class="selectListNumberOfLine" name="selectListNumberOfLine">
                        <option value="1" selected="selected">1 个字段</option>
                        <option value="2" >2 个字段</option>
                        <option value="3" >3 个字段</option>
                        <option value="4" >4 个字段</option>
                        <option value="5" >5 个字段</option>
                        <option value="10" >10 个字段</option>
                        <option value="100" >100 个字段</option>
                    </select>
                </label>
                <label>
                    <span>group by:</span>
                    <select class="groupItemSingleLine" name="groupItemSingleLine">
                        <option value="true">多行</option>
                        <option value="false" selected="selected">单行</option>
                    </select>
                </label>
                |
                <span>更多信息请查看帮助 (当前版本暂不支持格式化所有类型的sql)</span>
                |
                <label>
                    <span>Tab Size:</span>
                    <select id="tabSize" name="tabSize" onchange="adjustTabSize(this.value, '#p_translate_sql textarea')">
                        <option value="auto" selected>auto</option>
                        <option value="2">2</option>
                        <option value="4">4</option>
                        <option value="6">6</option>
                        <option value="8">8</option>
                    </select>
                </label>
            </div>

            <div class="col-xs-6 padding0 textarea_p">
                <textarea id="sql_translate_sql" name="sql" class="sql_textarea"
                          style="margin-top:5px;display: block!important;">
-- 请在此写入想要转换的sql语句

select city_id, '`特殊字符串`' as p, count(t.`id`) as `count_num`
from table t
where t.`a` = 1 and `b` = '2' and cast(c as int) = 4 and d is null
group by city_id,p
                </textarea>
            </div>

            <div class="col-xs-6 padding0 textarea_p">
                <textarea id="aggregate_translate_sql" name="output" class="sql_textarea" style="margin-top:5px;display: block!important;">#请点击转换按钮在此查看结果</textarea>
            </div>
        </div>
    </div>


    <div id="p_comment_sql" class="panel panel-primary tab-pane">
        <div class="panel-heading text-primary">
            <span class="p_title">添加 sql 注释</span>
        </div>

        <div class="panel-body sybn-api-demo collapse in">

            <div class="col-xs-12 padding0">
                <span>请在左侧输入sql然后</span>
                <span data-translate="commentSql" data-prompt="处理中,请稍后... (刷新表状态缓存时可能需要等待半分钟)" class="btn btn-success converSql submit btn-xs sql">点此添加注释</span>
                <span data-translate="commentSql" data-prompt="处理中,请稍后... (刷新表状态缓存时可能需要等待半分钟)" data-out_param="replaceTable" class="btn btn-default converSql submit btn-xs sql">添加注释并替换表名</span>
                <span data-translate="sql" class="btn btn-default converSql out_left submit btn-xs sql">格式化</span>
                <span data-translate="commentSqlClear" data-prompt="" class="btn btn-default converSql out_left submit btn-xs sql">清理注释</span>
                <span data-translate="appendParam" title="比如: select * from a in (?,?,?) Parameters: [null,2,3a] 转为 select * from a in (NULL,2,'3a')" data-prompt="" class="btn btn-default converSql out_left submit btn-xs sql">处理参数列表</span>
                <span>更多信息请查看帮助</span>
            </div>

            <div class="col-xs-6 padding0 textarea_p">
                <textarea id="sql_comment_sql" name="sql" class="sql_textarea" style="margin-top:5px;display: block!important;">
-- 请在此写入想要添加注释的 select 代码
select count(a) as a
from t
where b > 1
and c = 1
and d is not null;

-- 如果本页面无权访问相关表, 可直接附上 create table 语句
create table `t` (
    a int comment '字段a注释',
    b int comment '字段b注释',
    c int comment '字段c注释',
    d int comment '字段c注释',
    PRIMARY KEY (`id`),
    UNIQUE KEY `b` (`b`),
    KEY `c` (`c`)
);
                </textarea>
            </div>

            <div class="col-xs-6 padding0 textarea_p">
                <textarea id="aggregate_comment_sql" name="output" class="sql_textarea" style="margin-top:5px;display: block!important;">#请点击转换按钮在此查看结果</textarea>
            </div>
        </div>
    </div>


    <div id="p_translate_callees" class="panel panel-primary tab-pane">
        <div class="panel-heading text-primary">
            <span class="p_title">idea的调用链清理</span>
        </div>

        <div class="panel-body sybn-api-demo collapse in">

            <div class="col-xs-12 padding0">
                <span>请在左侧输入的idea的调用链, 然后</span>
                <span data-translate="callees" class="btn btn-success converSql submit btn-xs callees">点此处理</span>
                <span>更多信息请查看帮助</span>
            </div>

            <div class="col-xs-6 padding0 textarea_p">
                <textarea id="sql_textarea_callees" name="sql" class="sql_textarea"
                          style="margin-top:5px;display: block!important;">
# packageName=com.oristartech.  clearTest=true clearUtil=true clearJre=true clearSybnUtil=false clearVo=true converPrefixBlank='*   '  clearPackage=com.oristartech.response,com.oristartech.page
# 说明: 第一行写配置信息,一般不用改, 本行后面写 idea 导出的调用链. (Navigate > Call Hierarchy > 右侧选 Hierarchy 窗口选 caller 或者 callee 图标 > expand all 图标 > 导出图标)
String.equalsIgnoreCase(String)(5 usages)  (java.lang)
DbutilDaoPropertiesImpl.DbutilDaoPropertiesImpl(Properties, String)  (cn.sybn.data.dbutil)
    DbutilDaoPropertiesImpl.DbutilDaoPropertiesImpl(Properties, String, String)  (cn.sybn.data.dbutil)
MongoDaoPropertiesImpl.MongoDaoPropertiesImpl(Properties, String)  (cn.sybn.data.mongo.template)
    String.hashCode()  (java.lang)
    MongoDaoPropertiesImpl.MongoDaoPropertiesImpl(Properties, String, String)  (cn.sybn.data.mongo.template)
SolrDaoPropertiesImpl.SolrDaoPropertiesImpl(Properties, String)  (cn.sybn.data.solr)
    String.hashCode()  (java.lang)
    Math.abs(int)  (java.lang)
    SolrDaoPropertiesImpl.SolrDaoPropertiesImpl(Properties, String, String)  (cn.sybn.data.solr)
EsDaoPropertiesImpl.EsDaoPropertiesImpl(Properties, String)  (cn.sybn.data.es)
    String.hashCode()  (java.lang)
    Math.abs(int)  (java.lang)
    EsDaoPropertiesImpl.EsDaoPropertiesImpl(Properties, String, String)  (cn.sybn.data.es)
HBaseDaoPropertiesImpl.HBaseDaoPropertiesImpl(Properties, String)  (cn.sybn.util.data.hbase.template)
    HBaseDaoPropertiesImpl.HBaseDaoPropertiesImpl(Properties, String, String)  (cn.sybn.util.data.hbase.template)
                </textarea>
            </div>

            <div class="col-xs-6 padding0 textarea_p">
                <textarea id="aggregate_textarea_callees" name="output" class="sql_textarea"
                          style="margin-top:5px;display: block!important;">#请点击转换按钮在此查看结果</textarea>
            </div>
        </div>
    </div>


    <div id="p_translate_getFrom" class="panel panel-primary tab-pane">
        <div class="panel-heading text-primary">
            <span class="p_title">从 sql/xml 代码中获取中的 from 或 join 的表名</span>
        </div>

        <div class="panel-body sybn-api-demo collapse in">
            <div class="col-xs-12 padding0">
                <span>请在左侧输入 sql/xml 代码, 然后</span>
                <span data-translate="getFrom" class="btn btn-success converSql submit btn-xs getFrom">点此处理</span>
                <span>更多信息请查看帮助</span>
            </div>
            <div class="col-xs-6 padding0 textarea_p">
                <textarea id="sql_textarea_getFrom" name="sql" class="sql_textarea" style="margin-top:5px;display: block!important;">
// 黏贴任意语言的带有 sql 的代码都可以识别 (比如 bash, java, js, python 等), 部分特殊语法无法正常支持
function() {
    return "SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo\n" +
    "FROM Persons\n" +
    "INNER JOIN Orders\n" +
    "ON Persons.Id_P = " + id + "\n" +
    "ORDER BY Persons.LastName";
}
                </textarea>
            </div>
            <div class="col-xs-6 padding0 textarea_p">
                <textarea id="aggregate_textarea_getFrom" name="output" class="sql_textarea"
                          style="margin-top:5px;display: block!important;">#请点击转换按钮在此查看结果</textarea>
            </div>
        </div>
    </div>


    <div id="p_info" class="panel panel-primary tab-pane">
        <div class="panel-heading text-primary">
            <span class="p_title">关于</span>
        </div>
        <div class="panel-body sybn-api-demo collapse in">

            <div class="panel panel-warning w100">
                <div class="panel-heading text-primary">
                    <span class="p_title">帮助信息</span>
                </div>
                <div class="panel-body sybn-api-demo collapse in">
                    <p style="font-size:14px;">
                        <strong>文档地址</strong><br>
                        <span class="l_content">
                            <a href="http://doc.linpengfei.cn/2019/04/07/sql_query-convert-mongo_aggregate/"
                               target="_blank">http://doc.linpengfei.cn/2019/04/07/sql_query-convert-mongo_aggregate/</a>
                        </span>
                    </p>
                    <p style="font-size:14px;">
                        <strong>如何查询时间</strong><br>
                        <span class="l_content">mongo/solr的查询操作是区分数据类型的,如果需要查询时间可以使用如下方法:</span><br>
                        <span class="l_content">SQL: <span
                                class="text-primary">where datetime > "2019-01-01"</span></span><br>
                        <span class="l_content">mongo/solr: <span class="text-primary">where datetime > STR_TO_DATE('2019-01-21', '%Y-%m-%d')</span></span><br>
                        <span class="l_content">解析引擎会在生成mongo/solr查询前,将<span class="text-primary">STR_TO_DATE('2019-01-21', '%Y-%m-%d')</span>替换为对应的时间</span><br>
                        <span class="l_content">解析引擎可以识别大部分sql规范中的UDF函数, 比如: <span class="text-primary">where datetime > DATE_ADD(now(),INTERVAL -1 DAY)</span> 等</span><br>
                    </p>
                    <p style="font-size:14px;">
                        <strong>如何unwind</strong><br>
                        <span class="l_content">unwind是指将某一个数组列拆分为多行,具体在请百度搜索:<span class="text-primary">$unwind</span></span><br>
                        <span class="l_content">解析引擎可以转换mongo的unwind, 执行引擎可以对List/Stream执行unwind:</span><br>
                        <span class="l_content">语法: <span class="text-primary">select a,count(*) as b from table group by unwind(a_list) as a</span></span><br>
                        <span class="l_content">支持多字段 unwind</span>
                    </p>
                    <p style="font-size:14px;">
                        <strong>如何join</strong><br>
                        <span class="l_content">执行引擎可以对List/Stream/mysql/mongo/solr/hbase等数据源,执行互相join:</span><br>
                        <span class="l_content">当前页面仅仅是解析引擎的demo,无法体验执行引擎的功能</span><br>
                        <span class="l_content">文档地址: <a
                                href="http://doc.linpengfei.cn/2018/12/20/sybn-dao-multiple-impl/"
                                target="_blank">http://doc.linpengfei.cn/2018/12/20/sybn-dao-multiple-impl/</a></span>
                    </p>
                </div>
            </div>

            <div class="panel panel-success w100">
                <div class="panel-heading text-primary">
                    <span class="p_title">近期更新:</span>
                </div>
                <div class="panel-body collapse in">
                    <p>
                        <strong>2021-09</strong><br>
                        <span>从代码中获取 sql 中的 from 或 join 的表名</span><br>
                    </p>
                </div>
            </div>
        </div>
    </div>

</div>
<script>
window.onload = function() {
	var sql = getQueryString("sql");
	var sql_demo = getQueryString("sql_demo");
	if (sql_demo && getSybnDemoSql) {
		sql = getSybnDemoSql()[sql_demo];
	}
	if (sql) {
		var sql_editor = $('[name=sql]');
		sql_editor.val(sql);
	}

  // CodeMirror 有 bug 暂时不开启
  // initSqlEditor({});

  $('.CodeMirror').addClass("form-control");

  $("[name=dbtype]").val(getQueryString("dbType", "mysql"));

  $('body').on('click', '.converSql', function(e) {
    // 转换类型
	var outputType = $(this).data("translate");
	if (outputType == ""){
        alert("type 不支持!");
        return;
	}
	// 是否美化返回值
	var pretty = $(this).hasClass("pretty");
	// 是否输出到左表
	var out_left = $(this).hasClass("out_left");
	var $p = $(this).closest(".tab-pane");
	var $sql_input = $p.find('[name=sql]');
	var sql_editor = window[$sql_input.data("editor")];
	var dbType = $p.find('[name=dbtype]').val();
    var url2 = changeURLPar(window.location.href.toString(), "dbType", dbType);
    window.history.pushState({},0,url2);

	var $sql_output = $p.find('[name=output]');
	var sql = sql_editor ? sql_editor.getValue() : $p.find('[name=sql]').val();

	var prompt = $(this).data("prompt") || "处理中, 请稍后...";
    var aggregate_editor = window[$sql_output.data("editor")];
	if (!out_left) {
        $sql_output.val(prompt);
        if (aggregate_editor) {
            aggregate_editor.setValue(prompt);
        }
    }

    // 定义动态数据源
	var db_source = getQueryString("db_source");
    // 定义静态数据源
	var db_catalog = getQueryString("db_catalog");


    // 被提交的值
	var out_param = $(this).data("out_param") || "";
	var data = {query:sql, pretty:pretty, outputType:outputType, out_param:out_param, dbType:dbType, dbSource:db_source, dbCatalog:db_catalog};

    // ---- 以下为 sql 格式化参数
    // 是否将sql中的常量转为 ?
	var parameterized = $p.find("[name=parameterized]").val()
	// 每行字段数
	var selectListNumberOfLine = $p.find("[name=selectListNumberOfLine]").val()
	// group by 是否每个字段肚胀一行
	var groupItemSingleLine = $p.find("[name=groupItemSingleLine]").val()

	if (selectListNumberOfLine){
	    data.parameterized = parameterized;
	    data.selectListNumberOfLine = selectListNumberOfLine;
	    data.groupItemSingleLine = groupItemSingleLine;
	}


	// 防止双击触发单击
	$.ajax({
	     url: "api/sql/translate.json",
	     type:'post',
	     dataType:'json',
	     async:true,//异步请求
	     cache:false,
	     data: data,
	     //执行成功的回调函数
	     success:function(data) {
	      	  if (data.status && data.status >= 400){
	      	    alert(data.message ? "操作失败: " + data.message : "操作失败!");
	      	  } else if (!data.result){
	      	    alert(data.msg ? "操作失败: " + data.msg : "操作失败!");
	      	  } else {
                 if (out_left) {
                    $sql_input.val(data.output);
                 } else {
                    $sql_output.val(data.output);
                    var aggregate_editor = window[$sql_output.data("editor")];
                    if (aggregate_editor) {
                        aggregate_editor.setValue(data.output);
                    }
                 }
	         }
	     },
	     //执行失败或错误的回调函数
	     error:function(data) {
	         alert("操作失败");
	         $sql_output.val("");
	     }
	 });
  });

};

$(function () {
    // 从地址栏锚点获取档当前tab
	var h = window.location.hash.replace("#", "");
	if (h) {
	    $('a[href="#'+h+'"]').click();
	}

    // 将当前tab记录到地址栏锚点
    $('body').on('click', 'a[data-toggle="tab"]', function(e) {
        window.location.hash=this.getAttribute("href")
    })
});

</script>
</body>
</html>
